﻿/*
Вычисляем средневзвешанное по дням
Евгений Веприков 7 мая 2007 года
*/

CREATE PROCEDURE [dbo].[CalculateAvgReports2]
(
@DateB1 DATETIME,--дата начала периода уходимости
@DateE1 DATETIME,--дата окончания периода уходимости
@Dat1 DATETIME, --дата окончания периода расчета
@Dat2 DATETIME, --дата начала периода расчета
@DeptId INT, --идентификатор подразделения
@ContragentId INT -- для фильтра по контрагенту
)
AS
--таблица с результами отчета
DECLARE @Dates TABLE (date1 DATETIME PRIMARY KEY CLUSTERED, avg1 DECIMAL(19,2),
avg2 DECIMAL(19,2), UNIQUE(date1,avg1,avg2))

INSERT INTO @Dates(date1)
SELECT  CONVERT(DATETIME, CONVERT(VARCHAR(12), DocDate, 102), 102)
FROM         CA.Documents AS d
WHERE     (DepartmentId = 2) AND (DocTypeId = 3) AND 
(DocDate <= @Dat2) AND 
(DocDate >= @Dat1)
GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(12), DocDate, 102), 102)
--ORDER BY 1
--таблица со средневзвешанным
DECLARE @tmp1 table(id1 INT primary key clustered, qntty INT)
INSERT INTO @tmp1(id1,qntty)
SELECT  CD.Parties.GoodsId, SUM(-CA.GoodsDocLines.Qntty)
FROM         CA.Documents INNER JOIN
CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId
INNER JOIN CD.Parties ON CD.Parties.Id = CA.GoodsDocLines.PartyId
WHERE     (CA.Documents.DepartmentId = @DeptId) AND (CA.Documents.DocTypeId = 3) AND 
(CA.Documents.DocDate >= @DateB1 ) AND 
(CA.Documents.DocDate <= @DateE1)
GROUP BY CD.Parties.GoodsId
--select * from @tmp1
--таблица в которой дни продаж, товары проданные в этот день, средняя цена и количество проданных упаковок
--а также флаг, показывающий, продавался ли этот товар заврта тоже
DECLARE @tab TABLE ( Date DATETIME, goodsId INT, price DECIMAL(19,2), qntty INT, flag bit
UNIQUE (Date,goodsId,price,qntty,flag))
IF (@ContragentId IS NULL)
BEGIN
INSERT INTO @tab(Date, goodsId, price, qntty,flag)
SELECT  CONVERT(DATETIME, CONVERT(VARCHAR(12), d.DocDate, 102), 102), p.GoodsId,
AVG(g.RetailSum/g.Qntty), SUM(-g.Qntty),0
FROM         CA.Documents AS d INNER JOIN
CA.GoodsDocLines AS g ON d.Id = g.DocId INNER JOIN
CD.Parties AS p ON g.PartyId = p.Id 
WHERE     (d.DepartmentId = @DeptId) AND 
(d.DocDate >= @Dat1) 
AND (d.DocDate <= @Dat2) AND 
(d.DocTypeId = 3)
GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(12), d.DocDate, 102), 102), p.GoodsId
END
ELSE
BEGIN
INSERT INTO @tab(Date, goodsId, price, qntty,flag)
SELECT     CONVERT(DATETIME, CONVERT(VARCHAR(12), d.DocDate, 102), 102), 
p.GoodsId, CD.InputLines.SupplierPrice, SUM(- g.Qntty),0 
                      
FROM         CA.Documents AS d INNER JOIN
                      CA.GoodsDocLines AS g ON d.Id = g.DocId INNER JOIN
                      CD.Parties AS p ON g.PartyId = p.Id INNER JOIN
                      CD.InputLines ON p.InputLineId = CD.InputLines.Id
WHERE     (d.DepartmentId = @DeptId) AND (d.DocTypeId = 3) AND (d.DocDate >= @Dat1) 
AND (d.DocDate <= @Dat2) AND (CD.InputLines.ContragentId = @ContragentId)
GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(12), d.DocDate, 102), 102), 
p.GoodsId, CD.InputLines.SupplierPrice
END

UPDATE t1 SET flag = 1 FROM
@tab AS t1 INNER JOIN @tab t2 ON DATEADD(d,1,t1.Date) = t2.Date AND t1.goodsId = t2.goodsId
--расчитываем значение средневзвешанного на конкретную дату
UPDATE @Dates SET avg1 = 
(
SELECT SUM(tmp.qntty * t.price)/SUM(tmp.qntty)
FROM @tab AS t 
INNER JOIN @tmp1 AS tmp ON tmp.id1 = t.goodsId
WHERE date1 = t.Date AND t.flag = 1
)

UPDATE d2 SET d2.avg2 = d2.avg1 * 100 / d1.avg1
FROM @Dates AS d1 INNER JOIN @Dates AS d2
ON d1.date1 = DATEADD(d,-1,d2.date1)
SELECT date1, avg1, avg2 FROM @Dates
ORDER BY date1